﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.Specialized;

using System.Data;
using System.Data.OleDb;
using System.Data.Odbc;

using MySql.Data;
using MySql.Data.MySqlClient;

// Parsing SQL code in C#
//http://stackoverflow.com/questions/589096/parsing-sql-code-in-c
//http://msdn.microsoft.com/EN-US/library/bb738533.aspx
using System.Data.EntityClient;
using System.Data.SqlClient;

namespace WindowsFormsApplication1
{

    /// <remarks>
    /// Classe de persistência do banco de dados de layout
    /// </remarks>
    public class LayoutDatabase:GenericDatabase
    {
        //private LayoutTable layoutTables;

        public const string LAYOUT_TYPES_TABLE_NAME = "ArquivosTipos";
        public const string LAYOUT_RECTYP_TABLE_NAME = "MSysIMEXSpecs";
        public const string LAYOUT_COLUMS_TABLE_NAME = "MSysIMEXColumns";

        public const string LAYOUT_TYPES_TABLE_RECTYPE_COLNAME = "SpecName";
        public const string LAYOUT_TYPES_TABLE_RECTYPC_COLNAME = "LayoutCod";
        public const string LAYOUT_TYPES_TABLE_PARTYPE_COLNAME = "ParentSpecName";
        public const string LAYOUT_TYPES_TABLE_FILESTY_COLNAME = "ArquivosTiposEspExp";
        public const string LAYOUT_TYPES_TABLE_SQLSOUR_COLNAME = "LayoutRecSource";
        public const string COLNAME_FORMULATYPE = "FormulaType";

        /// <summary>
        ///  Obtem o conjunto de especificações de exportacão das linhas de detalhe
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        internal System.Data.DataTable GetFileLayout(string p)
        {
            System.Data.DataTable fileLayout = null;

            try
            {
                fileLayout = new System.Data.DataTable();
                fileLayout.TableName = LAYOUT_RECTYP_TABLE_NAME;

                System.Data.OleDb.OleDbCommand childCommand =
                    new System.Data.OleDb.OleDbCommand("SELECT * FROM " + LAYOUT_RECTYP_TABLE_NAME, 
                        (System.Data.OleDb.OleDbConnection)this.Connection);

                //DataTable childDataTable = new System.Data.DataTable();
                //childDataTable.TableName = currentTableName;
                //DataSet childDataSet = new System.Data.DataSet();
                //System.Data.OleDb.OleDbCommand childCommand =
                //    new System.Data.OleDb.OleDbCommand("SELECT * FROM " + currentTableName, this.mdbDatabase.Connection);
                //System.Data.OleDb.OleDbDataAdapter childDataAdapter = new System.Data.OleDb.OleDbDataAdapter(childCommand);

                //childDataAdapter.Fill(childDataSet);
                //childDataTable = childDataSet.Tables[0];
            }
            catch (Exception ex)
            {
                throw;
            }

            return fileLayout;
        }

        /// <summary>
        /// Obtem tabela com tipos de registros desse tipo de exportação
        /// </summary>
        /// <param name="currentLayoutDB"></param>
        /// <param name="expTypeName"></param>
        /// <returns></returns>
        internal System.Data.DataTable GetRecordTypesLayout(string expTypeName)
        {
            System.Data.DataTable recordTypesLayout = null;

            try
            {
                //SELECT MSysIMEXSpecs.*, ArquivosTipos.ArquivosTiposNome
                //FROM ArquivosTipos INNER JOIN MSysIMEXSpecs ON ArquivosTipos.ArquivosTiposCod = MSysIMEXSpecs.LayoutCod
                //WHERE (((ArquivosTipos.ArquivosTiposNome)="SERASA"));

                string SQL = "SELECT MSysIMEXSpecs.*, ArquivosTipos.ArquivosTiposNome " +
                        "FROM ArquivosTipos INNER JOIN MSysIMEXSpecs ON ArquivosTipos.ArquivosTiposCod = MSysIMEXSpecs.LayoutCod " +
                        "WHERE (((ArquivosTipos.ArquivosTiposNome)=\"" + expTypeName.Trim() + "\")) ORDER BY LayoutOrd;";

                recordTypesLayout = GetTableDataBySQL(SQL);
                recordTypesLayout.TableName = LAYOUT_RECTYP_TABLE_NAME;
            }
            catch (Exception ex)
            {
                throw;
            }

            return recordTypesLayout;
        }

        /// <summary>
        /// Obtem tabela com tipos de registros desse tipo de exportação
        /// </summary>
        /// <param name="currentLayoutDB"></param>
        /// <param name="expTypeName"></param>
        /// <returns></returns>
        internal System.Data.DataTable GetRecordTypesLayout(int expTypeCod)
        {
            System.Data.DataTable recordTypesLayout = null;

            try
            {
                recordTypesLayout = GetTableDataByName(LAYOUT_RECTYP_TABLE_NAME,
                    " " + LAYOUT_TYPES_TABLE_RECTYPC_COLNAME + " = " + expTypeCod);
                recordTypesLayout.TableName = LAYOUT_RECTYP_TABLE_NAME;
            }
            catch (Exception ex)
            {
                throw;
            }

            return recordTypesLayout;
        }

        /// <summary>
        /// Obtem uma tabela com a specificação de exportação das colunas de um tipo de linha de texto.
        /// </summary>
        /// <param name="p"></param>
        /// <param name="p_2"></param>
        /// <returns></returns>
        internal System.Data.DataTable GetColumnsLayout(string SpecName)//, string FieldName)
        {
            System.Data.DataTable columnsLayout = null;

            try
            {
                //SELECT MSysIMEXSpecs.SpecName, MSysIMEXColumns.*
                //FROM MSysIMEXSpecs INNER JOIN MSysIMEXColumns ON MSysIMEXSpecs.SpecID = MSysIMEXColumns.SpecID
                //WHERE (((MSysIMEXSpecs.SpecName)="teste"));

                string columnsLayoutSQL = "SELECT MSysIMEXSpecs.SpecName, MSysIMEXColumns.* " + 
                    "FROM MSysIMEXSpecs INNER JOIN MSysIMEXColumns ON MSysIMEXSpecs.SpecID = MSysIMEXColumns.SpecID " + 
                    "WHERE (((MSysIMEXSpecs.SpecName)=\"" +  SpecName + "\"))  ORDER BY Start; ";

                //columnsLayout = GetTableDataByName(LAYOUT_COLUMS_TABLE_NAME, "");
                columnsLayout = GetTableDataBySQL(columnsLayoutSQL);
                columnsLayout.TableName = LAYOUT_COLUMS_TABLE_NAME;
            }
            catch (Exception ex)
            {
                throw;
            }

            return columnsLayout;
        }

        /// <summary>
        /// Obtem tabela com tipos de registros de detalhes relacionadas a um tipo de registro de layout mestre
        /// </summary>
        /// <param name="ParentSpecName"></param>
        /// <param name="FieldName"></param>
        /// <returns></returns>
        internal System.Data.DataTable GetLayoutChildrenRecordTypes(string ParentSpecName)
        {
            System.Data.DataTable ChildTypesLayout = null;

            try
            {
                ChildTypesLayout.TableName = LAYOUT_RECTYP_TABLE_NAME;
                ChildTypesLayout = GetTableDataByName(LAYOUT_RECTYP_TABLE_NAME,
                    " " + LAYOUT_TYPES_TABLE_PARTYPE_COLNAME + " = '" + ParentSpecName + "'");
            }
            catch (Exception ex)
            {
                throw;
            }

            return ChildTypesLayout;
        }

        /// <summary>
        /// Obtem o valor do campo a partir da expressão e do DataSet mestre que está sendo exportado
        /// </summary>
        /// <param name="layoutRow"></param>
        /// <param name="expDataSource"></param>
        /// <param name="columnSpec"></param>
        /// <param name="currentExportSourceDB"></param>
        /// <param name="currentMasterDataRowNumber"></param>
        /// <returns></returns>
        internal string EvaluateTableValue(System.Data.DataRow layoutRow, System.Data.DataSet expDataSource, System.Data.DataRow columnSpec, ExportDatabase currentExportSourceDB, int currentMasterDataRowNumber)
        {

            string colValue = "";

            try
            {
                System.Data.DataTable tableValue = null;

                // Obtem o nome da tabela mestre que está sendo exportada 
                string masterSourceTableName = layoutRow["LayoutRecSource"].ToString();

                // Obtem o nome da tabela de onde se vai obter o valor deste campo que será exportado
                // a partir da expressão de valor, que deve estar na forma "[Tabela].[Campo]" ou simplesmente "Campo"
                string sourceTableName = UtilMDB.GetTableNameFromExpr(columnSpec["FieldValue"].ToString(), masterSourceTableName);

                // número do registro a ser lido da tabela origem do valor a ser exportado
                int sourceValueRecordNumber = 0;

                // caso a tabela seja a mesma da tabela mestre a ser exportada
                // ou se a tabela de origem de dados para este campo está no mesmo dataSet da tabela mestre
                if ((masterSourceTableName == sourceTableName) ||
                    (expDataSource.Tables.Contains(sourceTableName)))
                {
                    // Obtem o DataTable desta tabela de onde será o btido o valor do campo a ser exportado.
                    //tableValue = expDataSource.Tables[sourceTableName];
                    tableValue = expDataSource.Tables[0];

                    // Caso a tabela origem do valor seja a mesma mestre dos dados
                    if (masterSourceTableName == sourceTableName)
                    {
                        // O valor do campo será lido do mesmo registro atual da tabela mestre que está sendo exportada
                        sourceValueRecordNumber = currentMasterDataRowNumber;
                    }
                    // Caso a tabela origem seja outra tabela filha do mesmo DataSet da tabela mestre de exportação
                    else
                    {
                        // então o número do registro será o primeiro registro da tabela filha desse registro mestre
                        // TODO : o DataTable filho pode ter mais de um registro filho para cada registro mestre 
                        // Tratar esse caso, que precisa de um criterio a mais para restringir a um registro filho somente
                        // Possivelmente criando um campo de filtro no layout ou funções de agregação como SUM etc.
                        // Esta agregação deveria constar da origem dos dados da tabela mestre de exportação
                    }
                }
                // Caso a tabela especificada não esteja no mesmo DataSet da tabela master
                // será necessário obter um novo DataTable do banco de dados atual
                else
                {
                    tableValue = currentExportSourceDB.GetTableDataByName(sourceTableName);
                }

                // Caso tenha obtido o nome da tabela origem do valor com sucesso, então obtem o valor a ser exportado
                if (tableValue != null)
                {
                    string valueFieldName = UtilMDB.GetFieldNameFromExpr(columnSpec["FieldValue"].ToString(), "");

                    // Caso tenha obtido o nome do campo com sucesso, obtem o valor
                    if (valueFieldName != "")
                    {
                        colValue = tableValue.Rows[sourceValueRecordNumber][valueFieldName].ToString();
                    }

                }
            }
            catch (Exception ex)
            {                
                throw;
            }

            return colValue;
        }

        /// <summary>
        /// Avalia em tempo de execução uma expressão válida dentro do engine do MS-Access
        /// TODO : falta passar os objetos locais, nomes de variáveis para o script
        /// </summary>
        /// <param name="layoutRow"></param>
        /// <param name="expDataSource"></param>
        /// <param name="columnSpec"></param>
        /// <param name="currentExportSourceDB"></param>
        /// <param name="currentMasterDataRowNumber"></param>
        /// <returns></returns>
        internal string EvaluateScript(string expression,System.Data.DataRow layoutRow, System.Data.DataSet expDataSource, System.Data.DataRow columnSpec, ExportDatabase currentExportSourceDB, int currentMasterDataRowNumber)
        {

            string evalResult = "";

            try
            {
                //Microsoft.Office.Interop.Access.Application accessAppEngine = new Microsoft.Office.Interop.Access.Application();
                
                //// TODO : Testar a expressão "Date()" para obter a data de hoje
                //// TODO:accessAppEngine.Eval(expression)	{10/07/2011 00:00:00}	dynamic {System.DateTime}
                //evalResult = ((object)accessAppEngine.Eval(expression)).ToString();
            }
            catch (Exception ex)
            {
                throw;
            }

            return evalResult;
        }
    }

    /// <remarks>
    /// Classe de Persistência de Clientes
    /// </remarks>
    public class ExportDatabase : GenericDatabase
    {
        private string connectionString;

        /// <summary>
        /// Se está configurada um string de conexão e instancia usando a 
        /// string de conexão como parâmetro
        /// </summary>
        /// <param name="connectionString"></param>
        public ExportDatabase(string connectionString):base(connectionString)
        {
            // TODO: Complete member initialization
            this.connectionString = connectionString;
            
            //base.ConnectionString = connectionString;
            //base = new GenericDatabase(connectionString);
        }

        /// <summary>
        /// 
        /// Caso não haja a string de conexão, 
        /// instancia objeto de persistência do Banco de Dados MS-Access a ser exportado
        /// se estiver configurado um banco do tipo MS-Access, instancia sem parametros
        /// </summary>
        public ExportDatabase()
        {
            // TODO: Complete member initialization
        }

    }

    /// <remarks>
    /// Persistência genérica de bancos de dados MS-Access
    /// </remarks>
    public class GenericDatabase
    {
        private string filePath;
        private bool isOpen;

        private string currentPassword="";

        NameValueCollection connectionTokens = null;

        //private System.Data.OleDb.OleDbDataAdapter currentDataAdapter;
        private object currentDataAdapter;

        //public System.Data.OleDb.OleDbDataAdapter CurrentDataAdapter
        public object CurrentDataAdapter
        {
            get { return currentDataAdapter; }
            set { currentDataAdapter = value; }
        }

        public bool IsOpen
        {
            get { return isOpen; }
            set { isOpen = value; }
        }

        //private System.Data.OleDb.OleDbConnection connection;
        //private DatabaseConnection connection;
        private object connection;
        //private DatabaseConnection connection;

        private string connectionString = "";
        private string currentDatabase;
        private string currentUID;

        public string CurrentUID
        {
            get { return currentUID; }
            set { currentUID = value; }
        }
        private string currentServer;
        private string currentProvider;
        private string currentDataSource;
        private string currentDriver;
        private string currentPort;
        private string currentInitialCatalog;

        public string CurrentServer
        {
            get { return currentServer; }
            set { currentServer = value; }
        }

        public string ConnectionString
        {
            get { return connectionString; }
            set { connectionString = value; }
        }

        //public System.Data.OleDb.OleDbConnection Connection
        public object Connection
        {
            get { return connection; }
            set { connection = value; }
        }

        public string FilePath
        {
            get { return filePath; }
            set { filePath = value; }
        }

        private bool useGenericConnection = false;
        private string currentDataProvider;

        public bool UseGenericConnection
        {
            get { return useGenericConnection; }
            set { useGenericConnection = value; }
        }

        /// <summary>
        /// Abre um arquivo de banco de dados do MS-Access
        /// </summary>
        public void OpenMDB()
        {
            try
            {
                // Verifica se o arquivo ainda existe e está acessível para leitura
                if (System.IO.File.Exists(filePath))
                {
                    if (useGenericConnection)
                    {
                        connection = new DatabaseConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Persist Security Info=False");

                        ((DatabaseConnection)connection).Open();

                        if (((DatabaseConnection)connection).State == ConnectionState.Open)
                            //if (((System.Data.OleDb.OleDbConnection)connection).State == ConnectionState.Open)
                            isOpen = true;
                    }
                    else
                    {
                        Close();

                        connection = new System.Data.OleDb.OleDbConnection();

                        //Data Source=desktop1\sqlexpress;Initial Catalog=MERCATOR;Persist Security Info=True;User ID=sa;Password=gfaf61;
                        //Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\proj\Lifeware\clientes\atlantico\serasa\implementacao\proto\Serasa\Serasa\layout.mdb;

                        //DRIVER=MySQL ODBC 5.1 Driver;DESCRIPTION=AtlanticoTeste;
                        //SERVER=cpro0691.publiccloud.com.br;UID=atlanticoimpor;PASSWORD=luat220209is;DATABASE=atlanticoimportacao;PORT=3306

                        //"Provider=MSDASQL; DRIVER={MySQL ODBC 3.51Driver}; 
                        //SERVER= localhost; DATABASE=Your_MySQL_Database; UID= Your_Username; PASSWORD=Your_Password; OPTION=3" 

                        //Persist Security Info=False
                        ((System.Data.OleDb.OleDbConnection)connection).ConnectionString =
                            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Persist Security Info=False";

                        ((System.Data.OleDb.OleDbConnection)connection).Open();
                    }
                }

            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// Abre um arquivo de banco de dados SQL Server
        /// </summary>
        public void OpenSQL(string connectionString)
        {
            try
            {
                    Close();

                    //connection = new System.Data.OleDb.OleDbConnection();

                    //Data Source=desktop1\sqlexpress;Initial Catalog=MERCATOR;Persist Security Info=True;User ID=sa;Password=gfaf61;
                    // Provider=SQLOLEDB.1;Password=gfaf61;Persist Security Info=True;User ID=sa;Initial Catalog=MERCATOR;Data Source=www.lifeware.com.br\sqlexpress,2447
                    //Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\proj\Lifeware\clientes\atlantico\serasa\implementacao\proto\Serasa\Serasa\layout.mdb;
                    //Persist Security Info=False
                    ((System.Data.OleDb.OleDbConnection)connection).ConnectionString = connectionString;
                        //"Provider=SQLOLEDB.1;Password=gfaf61;Persist Security Info=True;User ID=sa;Initial Catalog=MERCATOR;Data Source=www.lifeware.com.br\sqlexpress,2447";

                    // Configurações do Banco de Dados do cliente Atlantico na LocaWeb
                    //Domínio: atlanticoimportacao.com

                    //Login: atlanticoimportacao
                    //Senha: at2202
                    //Senha banco de dados locaweb: luat220209if

                    Open("SQLOLEDB.1","www.lifeware.com.br\\sqlexpress,2447","MERCATOR","sa","gfaf61");
                    isOpen = true;

            }
            catch (Exception ex)
            {
                throw;
            }
        }
        
        /// <summary>
        /// Abre um arquivo de banco de dados genérico a partir de uma string de conexão OLEDB
        /// </summary>
        /// <param name="dataToExportConnString"></param>
        internal bool Open(string dataToExportConnString)
        {
            bool success = false;

            try
            {
                Close();

                string ADOdataProvider = currentDataProvider;

                // Verifica no tipo de string de conexão qual o tipo de provedor de dados (ODBC, OLEDB, .NET, SQL)
                if (GetConnectionType(dataToExportConnString) == "ODBC")
                {

                    //connection = new System.Data.Odbc.OdbcConnection();

                    // Atlantico - MySQL string de conexão, usando DSN Atlantico
                    // Provider=MSDASQL.1;Persist Security Info=True;Extended Properties="DSN=Atlantico;";Initial Catalog=atlanticoimportacao

                    ((System.Data.Odbc.OdbcConnection)connection).ConnectionString = dataToExportConnString;
                    ((System.Data.Odbc.OdbcConnection)connection).Open();

                    OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder(dataToExportConnString);
                    object passwordObj = null;
                    if (builder.TryGetValue("password", out passwordObj)) currentPassword = passwordObj.ToString();

                }
                else
                {
                    //connection = new System.Data.OleDb.OleDbConnection();

                    //Data Source=desktop1\sqlexpress;Initial Catalog=MERCATOR;Persist Security Info=True;User ID=sa;Password=gfaf61;
                    // Provider=SQLOLEDB.1;Password=gfaf61;Persist Security Info=True;User ID=sa;Initial Catalog=MERCATOR;Data Source=www.lifeware.com.br\sqlexpress,2447
                    //Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\proj\Lifeware\clientes\atlantico\serasa\implementacao\proto\Serasa\Serasa\layout.mdb;
                    //Persist Security Info=False

                    ((System.Data.OleDb.OleDbConnection)connection).ConnectionString = dataToExportConnString;

                    //"Provider=SQLOLEDB.1;Password=gfaf61;Persist Security Info=True;User ID=sa;Initial Catalog=MERCATOR;Data Source=www.lifeware.com.br\sqlexpress,2447";

                    ((System.Data.OleDb.OleDbConnection)connection).Open();
                }
                isOpen = true;

                success = true;
            }
            catch (Exception ex)
            {
                isOpen = false;
                //throw;
            }

            return success;
        }

        /// <summary>
        /// Verifica no tipo de string de conexão qual o tipo de provedor de dados (ODBC, OLEDB, .NET, SQL)
        /// </summary>
        /// <param name="dataToExportConnString"></param>
        /// <returns></returns>
        private string GetConnectionType(string dataToExportConnString)
        {
            string connectionType = "";

            try
            {
                string[] tokens = dataToExportConnString.Split(';');
                //System.Data.DataColumn[] columnCol = (System.Data.DataColumn[])columnSpec.ItemArray;
                //bool found = columnCol.Any(columnToFind => columnToFind.ColumnName == "FormulaType");
                bool found = tokens.Any(colProvider => colProvider.Contains("Provider"));
                if (found)
                {
                    string providerToken = tokens.First(colProvider => colProvider.Contains("Provider"));
                    string[] providerTokenItems = providerToken.Split('=');
                    if (providerTokenItems[1].ToUpper().Contains("MSDASQL"))
                        connectionType = "ODBC";
                    else if (providerTokenItems[1].ToUpper().Contains("SQLOLEDB"))
                        connectionType = "OLEDB";
                }
                else // default
                    connectionType = "OLEDB"; // "ODBC";

                // Atlantico - MySQL string de conexão, usando DSN Atlantico
                // Provider=MSDASQL.1;Persist Security Info=False;Data Source=Atlantico;Initial Catalog=atlanticoimportacao
            }
            catch (Exception ex)
            {
                throw;
            }

            return connectionType;
        }

        /// <summary>
        /// Abre um arquivo de banco de dados genérico
        /// </summary>
        public void Open(string provider, string dataSource, string database, string user, string password)
        {
            try
            {
                    Close();

                    string connectionString = "";

                    if (provider == "") return;

                    connectionString = "Provider=" + provider;
                    connectionString += ";Data Source=" + dataSource;
                    if (database != null && database != "") connectionString += ";Initial Catalog=" + database;
                    if (user != null && user != "") connectionString += ";User ID=" + user;
                    if (password != null && password != "") connectionString += ";Password=" + password;

                    //connection = new System.Data.OleDb.OleDbConnection();

                    //Data Source=desktop1\sqlexpress;Initial Catalog=MERCATOR;Persist Security Info=True;User ID=sa;Password=gfaf61;
                    // Provider=SQLOLEDB.1;Password=gfaf61;Persist Security Info=True;User ID=sa;Initial Catalog=MERCATOR;Data Source=www.lifeware.com.br\sqlexpress,2447
                    //Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\proj\Lifeware\clientes\atlantico\serasa\implementacao\proto\Serasa\Serasa\layout.mdb;
                    //Persist Security Info=False
                    ((System.Data.OleDb.OleDbConnection)connection).ConnectionString = connectionString;
                        //"Provider=SQLOLEDB.1;Password=gfaf61;Persist Security Info=True;User ID=sa;Initial Catalog=MERCATOR;Data Source=www.lifeware.com.br\sqlexpress,2447";

                    ((System.Data.OleDb.OleDbConnection)connection).Open();
                    isOpen = true;

            }
            catch (Exception ex)
            {
                throw;
            }
        }

        public void Close()
        {
            try
            {
                if (connection != null)// && (((System.Data.OleDb.OleDbConnection)connection).State == System.Data.ConnectionState.Open))
                {
                    if(connection.GetType() == (new System.Data.OleDb.OleDbConnection().GetType()))
                        ((System.Data.OleDb.OleDbConnection)connection).Close();
                    if (connection.GetType() == (new System.Data.Odbc.OdbcConnection().GetType()))
                        ((System.Data.Odbc.OdbcConnection)connection).Close();

                    isOpen = false;
                }
            }
            catch (Exception ex)
            {
                //throw;
            }
        }

        public string[] GetTablesNames()
        {
            string[] tableNames=null;

            try
            {
                System.Data.DataTable schemaTable = ((System.Data.OleDb.OleDbConnection)connection).GetSchema("Tables");
                tableNames=new string[schemaTable.Rows.Count];

                int rowCount = 0;
                foreach (System.Data.DataRow row in schemaTable.Rows)
                {
                    tableNames[rowCount] = row.ItemArray[2].ToString(); // nome da tabela no MS-Access
                    //tableNames[rowCount] = row.ItemArray[5].ToString(); // descrição da tabela no MS-Access

                    rowCount++;
                }

            }
            catch (Exception ex)
            {
                
                throw;
            }

            return tableNames;

        }

        public DataTable GetSchemaTableExample(string connectionString)
        {
            using (OleDbConnection connection = new
                       OleDbConnection(connectionString))
            {
                connection.Open();
                DataTable schemaTable = connection.GetOleDbSchemaTable(
                    OleDbSchemaGuid.Tables,
                    new object[] { null, null, null, "TABLE" });
                return schemaTable;
            }
        }

        /// <summary>
        /// TODO : verifica se em algum relacionamento dentro do schema do banco de dados
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataTable GetSchemaTable(string tableName)
        {
            DataTable schemaTable = null;
 
            try
            {
                if (this.connection.GetType().Equals(typeof(System.Data.OleDb.OleDbConnection)))
                {
                    using ((OleDbConnection)connection)
                    {
                        //((OleDbConnection)connection).Open();

                        //schemaTable = ((OleDbConnection)connection).GetOleDbSchemaTable(
                        //    //OleDbSchemaGuid.Tables,
                        //    //OleDbSchemaGuid.Constraint_Table_Usage, // ver se obtem relacionamentos
                        //    OleDbSchemaGuid.Foreign_Keys, //// ver se obtem relacionamentos
                        //    new object[] { null, null, null, "TABLE" });

                        //Foreign_Keys maps to the OLE DB FOREIGN_KEYS rowset. Unless otherwise specified, restriction columns are returned in the following order.

                        //PK_TABLE_CATALOG 
                        // Catalog name in which the primary key table is defined. A null value if the provider does not support catalogs. 

                        //PK_TABLE_SCHEMA 
                        // Unqualified schema name in which the primary key table is defined. A null value if the provider does not support schemas. 

                        //PK_TABLE_NAME 
                        // Primary key table name. 

                        //FK_TABLE_CATALOG 
                        // Catalog name in which the foreign key table is defined. A null value if the provider does not support catalogs. 

                        //FK_TABLE_SCHEMA 
                        // Unqualified schema name in which the foreign key table is defined. A null value if the provider does not support schemas. 

                        //FK_TABLE_NAME 
                        // Foreign key table name. 

                        //This code retrieves the foreign keys of all the tables in a Schema
                        schemaTable = ((OleDbConnection)connection).GetOleDbSchemaTable(
                            //OleDbSchemaGuid.Referential_Constraints, new Object[] {null,null,null});
                            OleDbSchemaGuid.Foreign_Keys, new Object[] { null, null, tableName, null, null, null });//tableName });

                        return schemaTable;
                    }
                }
                else if (this.connection.GetType().Equals(typeof(System.Data.Odbc.OdbcConnection)))
                {
                    // TODO : testar com conexoes ODBC
                    using ((OdbcConnection)connection)
                    {
                        //((OdbcConnection)connection).Open();
                        schemaTable = ((OdbcConnection)connection).GetSchema(
                            "",
                            new string[] { null, null, null, "TABLE" });
                        return schemaTable;
                    }
                }
                else if (this.connection.GetType().Equals(typeof(DatabaseConnection)))
                {
                    using ((DatabaseConnection)connection)
                    {
                        //((OdbcConnection)connection).Open();
                        schemaTable = ((DatabaseConnection)connection).GetSchema(
                            "",
                            new string[] { null, null, null, "TABLE" });
                        return schemaTable;
                    }
                }
            }
            catch (Exception ex)
            {                
                throw;
            }

            return schemaTable;
        }

        public string[] GetTablesDesc()
        {
            string[] tableNames = null;

            try
            {
                System.Data.DataTable schemaTable = ((System.Data.OleDb.OleDbConnection)connection).GetSchema("Tables");
                tableNames = new string[schemaTable.Rows.Count];

                int rowCount = 0;
                foreach (System.Data.DataRow row in schemaTable.Rows)
                {
                    tableNames[rowCount] = row.ItemArray[5].ToString(); // descrição da tabela no MS-Access
                    if(tableNames[rowCount]=="")
                        tableNames[rowCount] = row.ItemArray[2].ToString(); // nome da tabela no MS-Access


                    rowCount++;
                }

            }
            catch (Exception ex)
            {

                throw;
            }

            return tableNames;

        }

        public string[,] GetTablesDescName()
        {
            string[,] tableNames;

            try
            {
                System.Data.DataTable schemaTable = ((System.Data.OleDb.OleDbConnection)connection).GetSchema("Tables");
                tableNames = new string[schemaTable.Rows.Count, 2];

                int rowCount = 0;
                foreach (System.Data.DataRow row in schemaTable.Rows)
                {
                    tableNames[rowCount,0] = row.ItemArray[2].ToString(); // nome da tabela no MS-Access
                    tableNames[rowCount,1] = row.ItemArray[5].ToString(); // descrição da tabela no MS-Access

                    rowCount++;
                }

            }
            catch (Exception ex)
            {
                throw;
            }

            return tableNames;

        }

        /// <summary>
        ///  Obtem uma única tabela a partir de um comando SQL de seleção
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        internal System.Data.DataTable GetTableDataBySQL(string SQLcommandText)
        {
            return GetTableDataBySQL(SQLcommandText, "");
        }

        /// <summary>
        ///  Obtem uma única tabela a partir de um comando SQL de seleção
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        internal System.Data.DataTable GetTableDataBySQL(string SQLcommandText, string formulaType)
        {
            System.Data.DataTable tableData = null;

            try
            {
                System.Data.DataSet tableDataSet = new System.Data.DataSet();

                tableData = new System.Data.DataTable();
                //tableData.TableName = TableName;

                //if (typeof(T).IsValueType || typeof(T).Equals(typeof(string)))
                if (connection.GetType() == (new System.Data.OleDb.OleDbConnection()).GetType())
                {
                    System.Data.OleDb.OleDbCommand tableCommand =
                        new System.Data.OleDb.OleDbCommand(SQLcommandText, ((System.Data.OleDb.OleDbConnection)connection));

                    System.Data.OleDb.OleDbDataAdapter tableDataAdapter = new System.Data.OleDb.OleDbDataAdapter(tableCommand);

                    tableDataAdapter.Fill(tableDataSet);

                    currentDataAdapter = tableDataAdapter;
                }
                else if (connection.GetType() == (new System.Data.Odbc.OdbcConnection()).GetType())
                {
                    System.Data.Odbc.OdbcCommand tableCommand =
                        new System.Data.Odbc.OdbcCommand(SQLcommandText, ((System.Data.Odbc.OdbcConnection)connection));

                    System.Data.Odbc.OdbcDataAdapter tableDataAdapter = new System.Data.Odbc.OdbcDataAdapter(tableCommand);

                    tableDataAdapter.Fill(tableDataSet);

                    currentDataAdapter = tableDataAdapter;
                }

                if (tableDataSet.Tables.Count > 0)
                {
                    tableData = tableDataSet.Tables[0];

                    switch (formulaType)
                    {
                        case "":
                            tableData.TableName = GetTableNameFromSQL(SQLcommandText);
                            break;
                        case "SQL":
                            break;
                        default:
                            tableData.TableName = GetTableNameFromSQL(SQLcommandText);
                            break;
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }

            return tableData;
        }

        private string GetTableNameFromSQL(string SQLcommandText)
        {
            string tableName = "";

            try
            {
                SQLcommandText = SQLcommandText.ToUpper();
                string[] separator = new string[] { " FROM ", " WHERE ", " INNER "};
                string[] SQLtokens = SQLcommandText.Split(separator, StringSplitOptions.RemoveEmptyEntries);
                if ((SQLtokens != null) && (SQLtokens.Length > 1))
                {
                    tableName = SQLtokens[1].Trim();
                }
                else return "";
            }
            catch (Exception ex)
            {
                throw;
            }

            return tableName;
        }

        private string GetTableNameFromSQLTest(string SQLcommandText)
        {
            string tableName = "";

            try
            {

                // Specify the provider name, server and database.
                string providerName = this.currentProvider;//"System.Data.SqlClient";
                string serverName = this.currentServer;//".";
                string databaseName = this.currentDatabase;//"AdventureWorks";

                // Initialize the connection string builder for the
                // underlying provider.
                SqlConnectionStringBuilder sqlBuilder =
                    new SqlConnectionStringBuilder();

                // Set the properties for the data source.
                sqlBuilder.DataSource = serverName;
                sqlBuilder.InitialCatalog = databaseName;
                sqlBuilder.IntegratedSecurity = true;

                // Build the SqlConnection connection string.
                string providerString = sqlBuilder.ToString();

                // Initialize the EntityConnectionStringBuilder.
                EntityConnectionStringBuilder entityBuilder =
                    new EntityConnectionStringBuilder();

                //Set the provider name.
                entityBuilder.Provider = providerName;

                // Set the provider-specific connection string.
                entityBuilder.ProviderConnectionString = providerString;

                // Set the Metadata location.
                entityBuilder.Metadata = @"res://*/AdventureWorksModel.csdl|
                            res://*/AdventureWorksModel.ssdl|
                            res://*/AdventureWorksModel.msl";
                Console.WriteLine(entityBuilder.ToString());

                using (EntityConnection conn =
                    new EntityConnection(entityBuilder.ToString()))
                {
                    conn.Open();
                    Console.WriteLine("Just testing the connection.");
                    conn.Close();
                }
            }
            catch (Exception ex)
            { 
                throw;
            }

            return tableName;
        }

        /// <summary>
        ///  Obtem uma única tabela a partir do nome da tabela
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        //internal System.Data.DataTable GetTableDataByName(string TableName)
        public System.Data.DataTable GetTableDataByName(string TableName)
        {
            System.Data.DataTable tableData = null;

            try
            {
                tableData = GetTableDataByName(TableName, "");
            }
            catch (Exception ex)
            {
                throw;
            }

            return tableData;
        }

        /// <summary>
        ///  Obtem uma única tabela a partir do nome da tabela 
        ///  aplicando um filtro opcional WHERE no comando SELECT
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        internal System.Data.DataTable GetTableDataByName(string TableName, string WhereClause)
        {
            System.Data.DataTable tableData = null;

            try
            {
                tableData = new System.Data.DataTable();
                tableData.TableName = TableName;

                if (WhereClause != null && WhereClause != "")
                    WhereClause = " WHERE " + WhereClause;

                tableData = GetTableDataBySQL("SELECT * FROM " + TableName + WhereClause);
            }
            catch (Exception ex)
            {
                throw;
            }

            return tableData;
        }

        /// <summary>
        /// TODO : não implementado
        /// </summary>
        /// <returns></returns>
        public NameValueCollection GetTablesColection()
        {

            //TODO : não implementado
            throw new NotImplementedException();

            NameValueCollection tableNames = null;

            try
            {
                System.Data.DataTable schemaTable = ((System.Data.OleDb.OleDbConnection)connection).GetSchema("Tables");
                tableNames = new NameValueCollection();

                int rowCount = 0;
                foreach (System.Data.DataRow row in schemaTable.Rows)
                {
                    string key = "Name";

                    //tableNames[rowCount] = row.ItemArray[5].ToString(); // descrição da tabela no MS-Access
                    if (tableNames[rowCount] == "")

                        //tableNames[rowCount] = row.ItemArray[2].ToString(); // nome da tabela no MS-Access

                    //nodeAttributes.Add(attribute.Name, attribute.Value);

                    rowCount++;
                }

            }
            catch (Exception ex)
            {

                throw;
            }

            return tableNames;

        }

        /// <summary>
        /// How do I display the Connection string builder dialogue?
        /// http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/a8cfbbc4-ef91-428d-a81d-231e5039c8ff/
        /// </summary>
        public void ShowConnectionBuilder()
        {
            //This is the answer (kindly provided by Ben Fidge on another forum I found)
            MSDASC.DataLinksClass oClass = new MSDASC.DataLinksClass();

            object oCon = new ADODB.ConnectionClass();// new ADODB.ConnectionClass();

            (oCon as ADODB.ConnectionClass).ConnectionString = "Provider=SQLOLEDB.1";

            oClass.PromptEdit(ref oCon);

            //MessageBox.Show((oCon as ADODB.ConnectionClass).ConnectionString);

            //add references to the following COM libraries:
            //Microsoft ActiveX Data Objects 2.7 Library
            //Microsoft OLE DB Service Component 1.0 Type Library

             //c:\Program Files\Microsoft.NET\Primary Interop Assemblies\adodb.dll

            //see http://groups.google.com/group/microsoft.public.dotnet.framework.adonet/browse_thread/thread/3f3924426f831a7b/d488c14321cfd093?lnk=st&q=&rnum=3&hl=en#d488c14321cfd093

            //Also, at: http://www.codeproject.com/cs/database/DataLinks.asp

            //I dont know which came first?
        }

        public GenericDatabase(string connectionString)
        {
            try
            {
                if (GetConnectionType(connectionString) == "ODBC")
                {
                    connection = new System.Data.Odbc.OdbcConnection();
                    
                    OdbcConnectionStringBuilder connBuilder = new OdbcConnectionStringBuilder(connectionString);
                    connectionTokens = new NameValueCollection();
                    foreach (string key in connBuilder.Keys)
                    {
                        object value = null;
                        connBuilder.TryGetValue(key, out value);
                        connectionTokens.Add(key, value.ToString());
                        if (key == "Database") currentDatabase = value.ToString();
                        if (key == "Server") currentServer = value.ToString();
                        if (key == "UID") currentUID = value.ToString();
                        if (key == "password") currentPassword = value.ToString();
                        if (key == "Data Source") currentDataSource = value.ToString();
                        if (key == "driver") currentDriver = value.ToString();
                        if (key == "port") currentPort = value.ToString();
                        if (key == "Initial Catalog") currentInitialCatalog = value.ToString();

                        if (key == "provider") currentProvider = value.ToString();
                        else currentProvider = "MSDASQL"; // ODBC
                    }
                }
                else if (GetConnectionType(connectionString) == "OLEDB")
                {
                    connection = new System.Data.OleDb.OleDbConnection();

                    OleDbConnectionStringBuilder connBuilder = new OleDbConnectionStringBuilder(connectionString);
                    connectionTokens = new NameValueCollection();
                    foreach (string key in connBuilder.Keys)
                    {
                        object value = null;
                        connBuilder.TryGetValue(key, out value);
                        connectionTokens.Add(key, value.ToString());
                        if (key == "Database") currentDatabase = value.ToString();
                        if (key == "Server") currentServer = value.ToString();
                        if (key == "UID") currentUID = value.ToString();
                        if (key == "password") currentPassword = value.ToString();
                        if (key == "Data Source") currentDataSource = value.ToString();
                        if (key == "driver") currentDriver = value.ToString();
                        if (key == "port") currentPort = value.ToString();
                        if (key == "Initial Catalog") currentInitialCatalog = value.ToString();

                        if (key == "provider") currentProvider = value.ToString();
                        else currentProvider = "SQLOLEDB"; // OLEDB
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            } 
        }

        public GenericDatabase():this("")
        {
            //throw new System.NotImplementedException();
        }

        /// <summary>
        /// TODO : verifica se em algum relacionamento dentro do schema do banco de dados
        /// </summary>
        /// <param name="currentTableName"></param>
        /// <returns></returns>
        internal bool HasRelations(string currentTableName)
        {
            bool hasRelations = false;

            try
            {
                DataTable dt = GetSchemaTable(currentTableName);
                if (dt != null && dt.Rows.Count > 0) hasRelations = true;
            }
            catch (Exception ex)
            {
                throw;
            }

            return hasRelations;
        }

        internal DataTable GetChildRows(DataTable dataSource, int p, string currentTableName)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// This code retrieves the foreign keys of all the tables in a Schema
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="cn"></param>
        public void InferRelations(DataSet ds, System.Data.OleDb.OleDbConnection cn, string tableName)
        {
            System.Data.OleDb.OleDbDataAdapter adp = new System.Data.OleDb.OleDbDataAdapter("", cn);
            //we can put restrictions on the data we want to recieve
            Object[] restrictions = new Object[] { null, null, tableName, null };
            //this is the Foreign key data we need to create relations
            DataTable schemaTable = cn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Foreign_Keys, restrictions);

            //go through all the foreign keys displayed

            foreach (DataRow row in schemaTable.Rows)
            {
                string strTable = row["PK_TABLE_NAME"].ToString();
                string strChild = row["FK_TABLE_NAME"].ToString();
                string strParentColName = row["PK_COLUMN_NAME"].ToString();
                string strChildColName = row["FK_COLUMN_NAME"].ToString();

                // the relation name that will be created.
                string strRelationName = strTable + "_" + strParentColName + "_" + strChild + "_" + strChildColName;

                // create any non existing tables along with key information
                CreateAndfFillSchema(ds, adp, strTable);
                CreateAndfFillSchema(ds, adp, strChild);
                try
                {
                    DataColumn PrimaryColumn = ds.Tables[strTable].Columns[strParentColName];
                    DataColumn ChildColumn = ds.Tables[strChild].Columns[strChildColName];
                    ds.Relations.Add(strRelationName, PrimaryColumn, ChildColumn);
                }
                catch (Exception)
                {
                    //well get here if there's already such as relation, but we want to continue anyway.
                }
            }
        }

        /// <summary>
        /// This code retrieves the foreign keys of all the tables in a Schema
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="cn"></param>
        public void InferRelations(DataSet ds, object cn, string tableName)
        {
            //we can put restrictions on the data we want to recieve
            Object[] restrictions = new Object[] { null, null, tableName, null };
            DataTable schemaTable = null;

            object adp = null;

            if (cn.GetType().Equals(typeof(OleDbConnection)))
            {
                adp = new System.Data.OleDb.OleDbDataAdapter("",
                (OleDbConnection)cn);

                //this is the Foreign key data we need to create relations
                schemaTable = ((OleDbConnection)cn).GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Foreign_Keys, restrictions);
            }
            else if (cn.GetType().Equals(typeof(OdbcConnection)))
            {
                // tentar obter indices da tabela pelo MySQL Data Provider
                if ( ((OdbcConnection)cn).Driver.Contains("myodbc")) //myodbc5.dll
                {
                    //SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(); 
                    OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder(((OdbcConnection)cn).ConnectionString);

                    object passwordObj=null;
                    if (!builder.TryGetValue("password", out passwordObj))
                    {
                        if (currentPassword != "") builder.Add("password",currentPassword);
                        else return;
                    }

                    object server = null; object database = null; object uid=null; object password=null;
                    if( builder.TryGetValue("server", out server) &&
                        builder.TryGetValue("database", out database) &&
                        builder.TryGetValue("UID", out uid)  &&
                        builder.TryGetValue("password", out password))
                    {
                        string odbcConnString = ((OdbcConnection)cn).ConnectionString;
                        string MyConString = "SERVER=" + server.ToString() + ";" +
                            "DATABASE=" +  database.ToString() + ";" +
                            "UID=" + uid.ToString() + ";" +
                            "PASSWORD=" + password.ToString() + ";";

                        MySqlConnectionStringBuilder mysqlBuilder= new MySqlConnectionStringBuilder();

                        MySqlConnection mysqlCon = new MySqlConnection(MyConString);
                        mysqlCon.Open();

                        //schemaTable = mysqlCon.GetSchema("DatasourceInformation"); //("ForeignKeys", new string[] { "", "", "" });
                        //schemaTable = mysqlCon.GetSchema("Restrictions");//, new string[] { "", "" });
                        schemaTable = mysqlCon.GetSchema("Foreign Keys", new string[] { "", "", tableName });
                        //schemaTable = mysqlCon.GetSchema("Indexes", new string[] { "", "", tableName });

                        mysqlCon.Close();
                    }
                }
                else
                {
                    adp = new System.Data.Odbc.OdbcDataAdapter("",
                    (OdbcConnection)cn);

                    //this is the Foreign key data we need to create relations
                    string[] restrictionsString = new string[] { "", "", "", tableName };
                    //schemaTable = ((OdbcConnection)cn).GetSchema("", restrictionsString);
                    schemaTable = ((OdbcConnection)cn).GetSchema("Restrictions");
                    //schemaTable = ((OdbcConnection)cn).GetSchema("Indexes", restrictionsString);//"Indexes");//"restrictions");
                    
                    //schemaTable = ((OdbcConnection)cn).GetSchema("Indexes", new string[] { "", "", tableName });

                    schemaTable = ((OdbcConnection)cn).GetSchema("Indexes", new string[] { "", "", tableName, "" });
                }
            }
            else return;

            //go through all the foreign keys displayed
            
            foreach (DataRow row in schemaTable.Rows)
            {
                string strTable = row["PK_TABLE_NAME"].ToString();
                string strChild = row["FK_TABLE_NAME"].ToString();
                string strParentColName = row["PK_COLUMN_NAME"].ToString();
                string strChildColName = row["FK_COLUMN_NAME"].ToString();

                // the relation name that will be created.
                string strRelationName = strTable + "_" + strParentColName + "_" + strChild + "_" + strChildColName;

                // create any non existing tables along with key information
                if (cn.GetType().Equals(typeof(OleDbConnection)))
                {
                    CreateAndfFillSchema(ds, (OleDbDataAdapter)adp, strTable);
                    CreateAndfFillSchema(ds, (OleDbDataAdapter)adp, strChild);
                }
                else if (cn.GetType().Equals(typeof(OdbcConnection)))
                {
                    CreateAndfFillSchema(ds, (OdbcDataAdapter)adp, strTable);
                    CreateAndfFillSchema(ds, (OdbcDataAdapter)adp, strChild);
                }
                else return;

                try
                {
                    DataColumn PrimaryColumn = ds.Tables[strTable].Columns[strParentColName];
                    DataColumn ChildColumn = ds.Tables[strChild].Columns[strChildColName];
                    ds.Relations.Add(strRelationName, PrimaryColumn, ChildColumn);
                }
                catch (Exception)
                {
                    //well get here if there's already such as relation, but we want to continue anyway.
                }
            }
        }

        private void CreateAndfFillSchema(System.Data.DataSet ds, System.Data.Odbc.OdbcDataAdapter adp, string TableName)
        {
            if (!ds.Tables.Contains(TableName))
            {
                adp.SelectCommand.CommandText = "SELECT * FROM " + TableName;
                adp.FillSchema(ds, SchemaType.Source, TableName);

                //'Fill the table in the dataset
                //myCommand.CommandText = strSQL
                //myAdapter.SelectCommand = myCommand
                //myAdapter.Fill(myDataSet, tableName)

                adp.Fill(ds, TableName);
            }
        }

        private void CreateAndfFillSchema(System.Data.DataSet ds, System.Data.OleDb.OleDbDataAdapter adp, string TableName)
        {
            if (!ds.Tables.Contains(TableName))
            {
                adp.SelectCommand.CommandText = "SELECT * FROM " + TableName;
                adp.FillSchema(ds, SchemaType.Source, TableName);

                //'Fill the table in the dataset
                //myCommand.CommandText = strSQL
                //myAdapter.SelectCommand = myCommand
                //myAdapter.Fill(myDataSet, tableName)

                adp.Fill(ds, TableName);
            }
        }

        // Parsing SQL code in C#
        //http://stackoverflow.com/questions/589096/parsing-sql-code-in-c
        //Use Microsoft Entity Framework (EF)
        //http://msdn.microsoft.com/EN-US/library/bb738533.aspx
        private void ParseSQL(string SQLcommandText)
        {

            //It has a "Entity SQL" parser which builds an expression tree
            //using System.Data.EntityClient;
            //...

            if (this.connection.GetType().Equals(typeof(OleDbConnection)))
            {
                //System.Data.SqlClient Namespace
                //System.Data.Odbc Namespace
                //System.Data.OleDb Namespace

                // Specify the provider name, server and database.
                string providerName = "System.Data.OleDb";// this.currentProvider;//"System.Data.SqlClient";
                string serverName = this.currentServer;//".";
                string databaseName = this.currentDatabase;//"AdventureWorks";

                // Initialize the EntityConnectionStringBuilder.
                EntityConnectionStringBuilder entityBuilder =
                    new EntityConnectionStringBuilder();

                //Set the provider name.
                entityBuilder.Provider = providerName;

                OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder(((OleDbConnection)this.Connection).ConnectionString);
                // Build the SqlConnection connection string.
                string providerString = builder.ToString();

                // Set the Metadata location.
                entityBuilder.Metadata = @"res://*/AdventureWorksModel.csdl|
                            res://*/AdventureWorksModel.ssdl|
                            res://*/AdventureWorksModel.msl";

                // Set the provider-specific connection string.
                entityBuilder.ProviderConnectionString = providerString;

                //EntityConnection conn = new EntityConnection(myContext.Connection.ConnectionString);
                EntityConnection conn = new EntityConnection(entityBuilder.ToString());
                    //((OleDbConnection)this.Connection).ConnectionString);
                conn.Open();
            }
            //EntityCommand cmd = conn.CreateCommand();
            //cmd.CommandText = @"Select t.MyValue From MyEntities.MyTable As t";
            //var queryExpression = cmd.Expression;
            //....
            //conn.Close();
        }
    }
}

// Parsing SQL code in C#
//http://stackoverflow.com/questions/589096/parsing-sql-code-in-c
//Use Microsoft Entity Framework (EF)

//It has a "Entity SQL" parser which builds an expression tree

//using System.Data.EntityClient;
//...
//EntityConnection conn = new EntityConnection(myContext.Connection.ConnectionString);
//conn.Open();
//EntityCommand cmd = conn.CreateCommand();
//cmd.CommandText = @"Select t.MyValue From MyEntities.MyTable As t";
//var queryExpression = cmd.Expression;
//....
//conn.Close();

//Or something like that, check it out on MSDN

//And it's all on Ballmers tick :-)

//There is also one on codeproject

//http://www.codeproject.com/KB/dotnet/SQL_parser.aspx
	
//That takes "Entity-SQL", a dialect of SQL; I believe the OP means regular SQL, such as "Transact-SQL" (SQL Server's dialect). In short; this won't work. – Marc Gravell♦ Feb 26 '09 at 4:57
	
//As far as I can tell there are no clues in orgional question either way? So before we right this off shall we wait for @aaCog to confirm? – TFD Feb 26 '09 at 5:05
	
//Well, I meant SQL and not T-SQL. Also, i do not want to submit any commands to the db server but just want to do the processing over the SQL code which you consider as a simple text. – Archie Feb 26 '09 at 9:01
//1 	 
	
//I can't find an Expression property for EntityCommand on msdn. msdn.microsoft.com/en-us/library/… 

 
/**
 * @sample    : mycon.cs
 * @purpose   : Demo sample for ODBC.NET using MyODBC
 * @author    : Venu, <venu@mysql.com>
 *
 * (C) Copyright MySQL AB, 1995-2006
 *
 **/

 /* build command
  * 
  *  csc /t:exe 
  *      /out:mycon.exe mycon.cs 
  *      /r:Microsoft.Data.Odbc.dll 
  */ 


 //using Console = System.Console;
 //using Microsoft.Data.Odbc;

 namespace myodbc3
 {
  class mycon
  {
    static void Main2test(string[] args)
    {
      try
      { 
        //Connection string for MyODBC 2.50
        /*string MyConString = "DRIVER={MySQL};" + 
                             "SERVER=localhost;" +
                             "DATABASE=test;" +
                             "UID=venu;" +
                             "PASSWORD=venu;" +
                             "OPTION=3";
        */
        //Connection string for MyODBC 3.51
        string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" + 
                             "SERVER=localhost;" +
                             "DATABASE=test;" +
                             "UID=venu;" +
                             "PASSWORD=venu;" +
                             "OPTION=3";
                    
        //Connect to MySQL using MyODBC
        OdbcConnection MyConnection = new OdbcConnection(MyConString);    
        MyConnection.Open();
  
        Console.WriteLine("\n !!! success, connected successfully !!!\n");    

        //Display connection information
        Console.WriteLine("Connection Information:");   
        Console.WriteLine("\tConnection String:" + MyConnection.ConnectionString);    
        Console.WriteLine("\tConnection Timeout:" + MyConnection.ConnectionTimeout);    
        Console.WriteLine("\tDatabase:" + MyConnection.Database);   
        Console.WriteLine("\tDataSource:" + MyConnection.DataSource);
        Console.WriteLine("\tDriver:" + MyConnection.Driver);
        Console.WriteLine("\tServerVersion:" + MyConnection.ServerVersion);

        //Create a sample table
        OdbcCommand MyCommand = new OdbcCommand("DROP TABLE IF EXISTS my_odbc_net",MyConnection);
        MyCommand.ExecuteNonQuery();
        MyCommand.CommandText = "CREATE TABLE my_odbc_net(id int, name varchar(20), idb bigint)";
        MyCommand.ExecuteNonQuery();

        //Insert
        MyCommand.CommandText = "INSERT INTO my_odbc_net VALUES(10,'venu', 300)";        
        Console.WriteLine("INSERT, Total rows affected:" + MyCommand.ExecuteNonQuery());;

        //Insert
        MyCommand.CommandText = "INSERT INTO my_odbc_net VALUES(20,'mysql',400)";        
        Console.WriteLine("INSERT, Total rows affected:" + MyCommand.ExecuteNonQuery());

        //Insert
        MyCommand.CommandText = "INSERT INTO my_odbc_net VALUES(20,'mysql',500)";        
        Console.WriteLine("INSERT, Total rows affected:" + MyCommand.ExecuteNonQuery());

        //Update
        MyCommand.CommandText = "UPDATE my_odbc_net SET id=999 WHERE id=20";        
        Console.WriteLine("Update, Total rows affected:" + MyCommand.ExecuteNonQuery());

        //COUNT(*)        
        MyCommand.CommandText = "SELECT COUNT(*) as TRows FROM my_odbc_net";        
        Console.WriteLine("Total Rows:" + MyCommand.ExecuteScalar());

        //Fetch
        MyCommand.CommandText = "SELECT * FROM my_odbc_net";                
        OdbcDataReader MyDataReader;
        MyDataReader =  MyCommand.ExecuteReader();
        while (MyDataReader.Read())
        {
         if(string.Compare(MyConnection.Driver,"myodbc3.dll") == 0) {
           Console.WriteLine("Data:" + MyDataReader.GetInt32(0) + " " +
                                       MyDataReader.GetString(1) + " " +
                                       MyDataReader.GetInt64(2)); //Supported only by MyODBC 3.51
         }
         else {            
           Console.WriteLine("Data:" + MyDataReader.GetInt32(0) + " " +
                                       MyDataReader.GetString(1) + " " +                                         
                                       MyDataReader.GetInt32(2)); //BIGINTs not supported by MyODBC
         }
        }

        //Close all resources
        MyDataReader.Close();
        MyConnection.Close();
      }
      catch (OdbcException MyOdbcException)//Catch any ODBC exception ..
      {
        for (int i=0; i < MyOdbcException.Errors.Count; i++)
        {
          Console.Write("ERROR #" + i + "\n" +
            "Message: " + MyOdbcException.Errors[i].Message + "\n" +
            "Native: " + MyOdbcException.Errors[i].NativeError.ToString() + "\n" +
            "Source: " + MyOdbcException.Errors[i].Source + "\n" +
            "SQL: " + MyOdbcException.Errors[i].SQLState + "\n");
        }
      }
    }
  }
 }

//List all the indexes in a MySQL database
/*
select * from information_schema.statistics 
 */

//dotConnect for MySQL	support@devart.com
//Retrieving Metadata
 // http://www.devart.com/dotconnect/mysql/docs/MetaData.html
/*
static void GetTableInfo(MySqlConnection myDbConnection, string tableName)
{
  myDbConnection.Open();
  DataTable myDataTable = myDbConnection.GetSchema(
  "Columns", new string[] { "Test", tableName });
  for (int i = 0; i < myDataTable.Columns.Count; i++)
  {
    Console.Write(myDataTable.Columns[i].Caption + "\t");
  }
  Console.WriteLine();
  foreach (DataRow myRow in myDataTable.Rows)
  {
    foreach (DataColumn myCol in myDataTable.Columns)
    {
      Console.Write(myRow[myCol] + "\t");
    }
    Console.WriteLine();
  }
  myDbConnection.Close();
}

 */



//This code retrieves the foreign keys of all the tables in a Schema

//Foreign_Keys maps to the OLE DB FOREIGN_KEYS rowset. Unless otherwise specified, restriction columns are returned in the following order.

//Restriction column 
// CLR data type 
// Description 

//PK_TABLE_CATALOG 
// string 
// Catalog name in which the primary key table is defined. A null value if the provider does not support catalogs. 

//PK_TABLE_SCHEMA 
// string 
// Unqualified schema name in which the primary key table is defined. A null value if the provider does not support schemas. 

//PK_TABLE_NAME 
// string 
// Primary key table name. 

//FK_TABLE_CATALOG 
// string 
// Catalog name in which the foreign key table is defined. A null value if the provider does not support catalogs. 

//FK_TABLE_SCHEMA 
// string 
// Unqualified schema name in which the foreign key table is defined. A null value if the provider does not support schemas. 

//FK_TABLE_NAME 
// string 
// Foreign key table name. 
//

//# re: Automatically Create DataRelations Based On OleDB Schema
/*
public static void InferRelations( DataSet ds, System.Data.OleDb.OleDbConnection cn )
{
System.Data.OleDb.OleDbDataAdapter adp = new System.Data.OleDb.OleDbDataAdapter("", cn);
//we can put restrictions on the data we want to recieve
Object[] restrictions = new Object[] {null, null, null, null};
//this is the Foreign key data we need to create relations
DataTable schemaTable = cn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Foreign_Keys, restrictions);

//go through all the foreign keys displayed

foreach(DataRow row in schemaTable.Rows)
{
string strTable = row["PK_TABLE_NAME"].ToString();
string strChild = row["FK_TABLE_NAME"].ToString();
string strParentColName = row["PK_COLUMN_NAME"].ToString();
string strChildColName = row["FK_COLUMN_NAME"].ToString();

// the relation name that will be created.
string strRelationName = strTable + "_" + strParentColName + "_" + strChild + "_" + strChildColName;

// create any non existing tables along with key information
CreateAndfFillSchema(ds, adp, strTable);
CreateAndfFillSchema(ds, adp, strChild);
try
{
DataColumn PrimaryColumn = ds.Tables[strTable].Columns[strParentColName];
DataColumn ChildColumn = ds.Tables[strChild].Columns[strChildColName];
ds.Relations.Add(strRelationName, PrimaryColumn, ChildColumn);
}
catch( Exception )
{
//well get here if there's already such as relation, but we want to continue anyway.
}
}
}

private static void CreateAndfFillSchema( System.Data.DataSet ds, System.Data.OleDb.OleDbDataAdapter adp, string TableName)
{
if( ! ds.Tables.Contains(TableName) )
{
adp.SelectCommand.CommandText = "SELECT * FROM " + TableName;
adp.FillSchema(ds, SchemaType.Source, TableName);
}
} 

*/

// MySQL conection
/*
 * MySQL conection
private void button1_Click(object sender, System.EventArgs e)
		{
			string MyConString = "SERVER=localhost;" +
				"DATABASE=mydatabase;" +
				"UID=testuser;" +
				"PASSWORD=testpassword;";
			MySqlConnection connection = new MySqlConnection(MyConString);
			MySqlCommand command = connection.CreateCommand();
			MySqlDataReader Reader;
			command.CommandText = "select * from mycustomers";
			connection.Open();
			Reader = command.ExecuteReader();
			while (Reader.Read())
			{
				string thisrow = "";
				for (int i= 0;i<Reader.FieldCount;i++)
						thisrow+=Reader.GetValue(i).ToString() + ",";
				listBox1.Items.Add(thisrow);
			}
			connection.Close();
		}
 */

//schemaTable = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, new Object[] { null, theSchema, null });

//1. DataTable data = dc.GetOleDbSchemaTable( OleDbSchemaGuid.Foreign_Keys,
//        new object[] { null, null, "TableA", null, null, null } );

//OK. Returned data table contains correct information about foreign key.

//2. DataTable data = dc.GetOleDbSchemaTable( OleDbSchemaGuid.Foreign_Keys,
//        new object[] { null, null, null, null, null, "TableB" } );

//Bug. Returned data table is empty. Use FK_TABLE_NAME restriction
//column does not work in anyway.

/*
//how to find relationships in Access MDB using OleDB

Dim AccessConnection As System.Data.OleDb.OleDbConnection

Dim AccessDataTable As DataTable

Try

AccessConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Test Files\db1 XP.mdb")

AccessConnection.Open()

AccessDataTable = AccessConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Foreign_Keys, New Object() {Nothing, Nothing, "TABLE_1"})

DataGrid1.DataSource = AccessDataTable

Dim RowCount As Int32

For RowCount = 0 To AccessDataTable.Rows.Count - 1

'Console.WriteLine(AccessDataTable.Rows(RowCount)!COLUMN_NAME.ToString)

Next RowCount

Catch ex As Exception

MessageBox.Show(ex.Message)

Finally

AccessConnection.Close()

End Try

*/

/*
 * 
Hello guys,
I found some code that solved my problem so I thought this can be useful.

This is the code:

DataTable schemaTable;

//This code retrieves the foreign keys of all the tables in a Schema
schemaTable = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, new Object[] {null, theSchema, null});

foreach(DataRow myRow in schemaTable.Rows)
{
String PK_TABLE_NAME = myRow ["PK_TABLE_NAME"] as String;
String PK_COLUMN_NAME = myRow ["PK_COLUMN_NAME"] as String;
String FK_TABLE_NAME = myRow ["FK_TABLE_NAME"] as String;
String FK_COLUMN_NAME = myRow ["FK_COLUMN_NAME"] as String;
}

Where:

PK_TABLE_NAME = The table that contains a Primary Key
PK_COLUMN_NAME = The Field used as Primary Key
FK_TABLE_NAME = The table that has a relation with the table that contains the Primary key (PK_TABLE_NAME)
FK_COLUMN_NAME = The field name that is used as a foreign key and is contained in the related table (FK_TABLE_NAME)

Hope this could help others.
:WAVE:

*/

/*
 
string stdInfo = string.Empty;

DataRow[] drStudent = null;
DataRow drCourses = null;
DataRow[] drStdCrs = null;

drStudent = universityDS.Tables["Students"]
    .Select(string.Format("StudentID='{0}'", txtStudentID.Text));

stdInfo += string.Format("Student {0} {1}:\nTaking Courses:",
    drStudent[0]["FirstName"].ToString().Trim(),
    drStudent[0]["LastName"].ToString().Trim());

drStdCrs = drStudent[0].GetChildRows(
    universityDS.Relations["FK_TakesCourses_Students"]);

//Can I optimize here? Is there a better way to code this
if (drStdCrs.Length > 0)
{
    for (int i = 0; i < drStdCrs.Length; i++)
    {
        drCourses = drStdCrs[i].GetParentRow(
            universityDS.Relations["FK_TakesCourses_Courses"]);

        stdInfo += string.Format("\nCourse: {0}",
            drCourses["CourseName"]);
    }               
}

MessageBox.Show(stdInfo);
*/

/*
 * 
 * // Loading the whole database into a DataSet 


  Protected Function getDataSetAndFill(ByRef connection As OleDb.OleDbConnection, Optional ByVal isExportSchema As Boolean = True) As DataSet

        Dim myDataSet As New DataSet
        Dim myCommand As New OleDb.OleDbCommand
        Dim myAdapter As New OleDb.OleDbDataAdapter

        myCommand.Connection = connection

        'Get Database Tables
        Dim tables As DataTable = connection.GetOleDbSchemaTable( _
            System.Data.OleDb.OleDbSchemaGuid.Tables, _
            New Object() {Nothing, Nothing, Nothing, "TABLE"})

        'iterate through all tables
        Dim table As DataRow
        For Each table In tables.Rows

            'get current table's name
            Dim tableName As String = table("TABLE_NAME")

            Dim strSQL = "SELECT * FROM " & "[" & tableName & "]"

            Dim adapter1 As New OleDb.OleDbDataAdapter(New OleDb.OleDbCommand(strSQL, connection))
            adapter1.FillSchema(myDataSet, SchemaType.Source, tableName)

            'Fill the table in the dataset
            myCommand.CommandText = strSQL
            myAdapter.SelectCommand = myCommand
            myAdapter.Fill(myDataSet, tableName)
        Next

        ''''''''''''''''''''''''''''''''''''''
        '''' Add relationships to dataset ''''
        ''''''''''''''''''''''''''''''''''''''

        'First, get relationships names from database (as well as parent table and child table names)
        Dim namesQuery As String = "SELECT DISTINCT szRelationship, szReferencedObject, szObject " & _
                                    "FROM MSysRelationships"
        Dim namesCommand As New System.Data.OleDb.OleDbCommand(namesQuery, connection)
        Dim namesAdapter As New System.Data.OleDb.OleDbDataAdapter(namesCommand)
        Dim namesDataTable As New DataTable
        namesAdapter.Fill(namesDataTable)

        'Now, get MSysRelationship from database
        Dim relationsQuery As String = "SELECT * FROM MSysRelationships"
        Dim command As New System.Data.OleDb.OleDbCommand(relationsQuery, connection)
        Dim adapter As New System.Data.OleDb.OleDbDataAdapter(command)
        Dim relationsDataTable As New DataTable
        adapter.Fill(relationsDataTable)

        Dim relationsView As DataView = relationsDataTable.DefaultView
        Dim relationName As String
        Dim parentTableName As String
        Dim childTablename As String
        Dim row As DataRow

        For Each relation As DataRow In namesDataTable.Rows
            relationName = relation("szRelationship")
            parentTableName = relation("szReferencedObject")
            childTablename = relation("szObject")

            'Keep only the record of the current relationship
            relationsView.RowFilter = "szRelationship = '" & relationName & "'"

            'Declare two arrays for parent and child columns arguments
            Dim parentColumns(relationsView.Count - 1) As DataColumn
            Dim childColumns(relationsView.Count - 1) As DataColumn

            For i As Integer = 0 To relationsView.Count - 1
                parentColumns(i) = myDataSet.Tables(parentTableName). _
                                Columns(relationsView.Item(i)("szReferencedColumn"))
                childColumns(i) = myDataSet.Tables(childTablename). _
                                Columns(relationsView.Item(i)("szColumn"))
            Next

            Dim newRelation As New DataRelation(relationName, parentColumns, childColumns, False)
            myDataSet.Relations.Add(newRelation)
        Next

        If isExportSchema Then
            Dim schemaName = GetXmlSchemaFileName()
            If File.Exists(schemaName) Then File.SetAttributes(schemaName, FileAttributes.Normal)
            myDataSet.WriteXmlSchema(schemaName)
        End If
        Return myDataSet
    End Function

*/

/*
 *
OleDbSchemaGuid.Assertions;
OleDbSchemaGuid.Catalogs;
OleDbSchemaGuid.Character_Sets;
OleDbSchemaGuid.Check_Constraints;
OleDbSchemaGuid.Check_Constraints_By_Table;
OleDbSchemaGuid.Collations;
OleDbSchemaGuid.Column_Domain_Usage;
OleDbSchemaGuid.Column_Privileges;
OleDbSchemaGuid.Columns;
OleDbSchemaGuid.Constraint_Column_Usage;
OleDbSchemaGuid.Constraint_Table_Usage;
OleDbSchemaGuid.DbInfoKeywords;
OleDbSchemaGuid.DbInfoLiterals;
OleDbSchemaGuid.Foreign_Keys;
OleDbSchemaGuid.Indexes;
OleDbSchemaGuid.Key_Column_Usage;
OleDbSchemaGuid.Primary_Keys;
OleDbSchemaGuid.Procedure_Columns;
OleDbSchemaGuid.Procedure_Parameters;
OleDbSchemaGuid.Procedures;
OleDbSchemaGuid.Provider_Types;
OleDbSchemaGuid.Referential_Constraints;
OleDbSchemaGuid.SchemaGuids;
OleDbSchemaGuid.Schemata;
OleDbSchemaGuid.Sql_Languages;
OleDbSchemaGuid.Statistics;
OleDbSchemaGuid.Table_Constraints;
OleDbSchemaGuid.Table_Privileges;
OleDbSchemaGuid.Table_Statistics;
OleDbSchemaGuid.Tables;
OleDbSchemaGuid.Tables_Info;
OleDbSchemaGuid.Translations;
OleDbSchemaGuid.Trustee;
OleDbSchemaGuid.Usage_Privileges;
OleDbSchemaGuid.View_Column_Usage;
OleDbSchemaGuid.View_Table_Usage;
OleDbSchemaGuid.Views;

*/